using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Transactions;
using Common.Utilities;

namespace Workflows.Components.DALC
{
    /// <summary>
    /// 
    /// </summary>
    public class JobDALC
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="job"></param>
        public static void SaveJob(Job job)
        {
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn=new SqlConnection(connStr);
            TransactionScope scope = new TransactionScope();
            try
            {
                conn.Open();
               
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SaveJob";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter jobIDParam=new SqlParameter("@JobID",job.JobID.ToString());
                SqlParameter runIDparam=new SqlParameter("@RunID",job.RunID.ToString());
                SqlParameter componentIDparam = new SqlParameter("@ComponentID", job.ComponentID.ToString());
                SqlParameter componentTypeNameparam = new SqlParameter("@ComponentTypeName", job.ComponentTypeName);
                string dependency = string.Empty;
                if(job.Dependency !=null && job.Dependency.Count>0)
                {
                    for(int i=0;i<job.Dependency.Count;i++)
                    {
                        dependency += job.Dependency[i].ToString();
                        if(i<job.Dependency.Count -1)
                        {
                            dependency += ",";
                        }
                    }
                }
                SqlParameter dependencyParam=new SqlParameter("@Dependency", dependency);
                SqlParameter cancelParam=new SqlParameter("@Cancelled",job.Cancelled);
                SqlParameter hasErrorParam=new SqlParameter("@HasError",job.HasError);
                SqlParameter isFirstParam=new SqlParameter("@IsFirstJob",job.IsFirstJob);
                SqlParameter isLastParam=new SqlParameter("@IsLastJob",job.IsLastJob);
                SqlParameter jobInstructionTypeNameParam=new SqlParameter("@JobInstructionTypeName",job.JobInstructionTypeName);
                SqlParameter jobOutputTypeName=new SqlParameter("@JobOutputTypeName",job.JobOutputTypeName);
                SqlParameter instructionIDParam=new SqlParameter("@JobInstructionID",job.JobInstructionID);
                SqlParameter outputIDParam=new SqlParameter("@JobOutputID",job.JobOutputID);
                SqlParameter priorityParam=new SqlParameter("@Priority",job.Priority);
                SqlParameter receiveTimeParam=new SqlParameter("@ReceiveTime",job.ReceiveTime);
                SqlParameter submitTimeParam=new SqlParameter("@SubmissionTime",job.SubmissionTime);
                SqlParameter startTimeParam=new SqlParameter("@StartTime",job.StartTime);
                SqlParameter finishTimeParam=new SqlParameter(@"FinishTime",job.FinishTime);

                cmd.Parameters.AddRange(new SqlParameter[]
                                            {
                                                jobIDParam, runIDparam, componentIDparam,
                                                componentTypeNameparam, dependencyParam,
                                                cancelParam, hasErrorParam, 
                                                isFirstParam, isLastParam,
                                                jobInstructionTypeNameParam,jobOutputTypeName,
                                                instructionIDParam, outputIDParam,
                                                priorityParam, receiveTimeParam, 
                                                submitTimeParam, startTimeParam,
                                                finishTimeParam
                                            });
                cmd.ExecuteNonQuery();
                scope.Complete();
            }
            catch(Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                scope.Dispose();
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="componentTypeNames"></param>
        /// <returns></returns>
        public static List<Job> CheckoutNewJobs(List<string> componentTypeNames)
        {
            List<Job> jobs=new List<Job>();
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            TransactionScope scope = new TransactionScope();
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                string selectSql = "select * from Jobs where ReceiveTime <'" +
                                  (new DateTime(2000,1,1)).ToString() + 
                                  "' and ComponentTypeName in (";
                for (int i = 0; i < componentTypeNames.Count;i++)
                {
                    selectSql += "'" + componentTypeNames[i] + "'";
                    if(i<componentTypeNames.Count -1)
                    {
                        selectSql += ",";
                    }
                    else
                    {
                        selectSql += ")";
                    }
                }
                cmd.CommandText = selectSql;
                cmd.CommandType = CommandType.Text;
                SqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read())
                {
                    Job job=new Job();
                    job.JobID=new Guid(reader["JobID"].ToString());
                    job.RunID=new Guid(reader["RunID"].ToString());
                    job.ComponentID=new Guid(reader["ComponentID"].ToString());
                    job.ComponentTypeName = reader["ComponentTypeName"].ToString();
                    job.Dependency=new List<Guid>();
                    string allDependency = reader["Dependency"].ToString();
                    if(!string.IsNullOrEmpty(allDependency))
                    {
                        string[] depencendies = allDependency.Split(new char[] {','});
                        foreach(string dependency in depencendies)
                        {
                            Guid depend=new Guid(dependency);
                            job.Dependency.Add(depend);
                        }
                    }
                    job.Cancelled = bool.Parse(reader["Cancelled"].ToString());
                    job.HasError = bool.Parse(reader["HasError"].ToString());
                    job.IsFirstJob = bool.Parse(reader["IsFirstJob"].ToString());
                    job.IsLastJob = bool.Parse(reader["IsLastJob"].ToString());
                    job.JobInstructionTypeName = reader["JobInstructionTypeName"].ToString();
                    job.JobOutputTypeName = reader["JobOutputTypeName"].ToString();
                    job.JobInstructionID = int.Parse(reader["JobInstructionID"].ToString());
                    job.JobOutputID = int.Parse(reader["JobOutputID"].ToString());
                    job.Priority = int.Parse(reader["Priority"].ToString());
                    job.ReceiveTime = DateTime.Parse(reader["ReceiveTime"].ToString());
                    job.SubmissionTime = DateTime.Parse(reader["SubmissionTime"].ToString());
                    job.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                    job.FinishTime = DateTime.Parse(reader["FinishTime"].ToString());
                    jobs.Add(job);
                }
                reader.Close();

                List<Job> jobsReady=new List<Job>();
                if(jobs.Count>0)
                {
                    foreach(Job job in jobs)
                    {
                        if(job.Dependency ==null || job.Dependency.Count==0)
                        {
                            jobsReady.Add(job);
                        }
                        else
                        {
                            bool canAddThisJob = true;
                            foreach(Guid dependentJobID in job.Dependency)
                            {
                                cmd.CommandText = "select FinishTime from Jobs where RunID='" +
                                                  job.RunID.ToString() + "' and ComponentID='" +
                                                  dependentJobID.ToString() + "'";
                                reader = cmd.ExecuteReader();
                                if(reader.Read())
                                {
                                    DateTime finishTime = reader.GetDateTime(0);
                                    if(finishTime >TimeUtil.DbMinTime && finishTime<DateTime.Now)
                                    {
                                        canAddThisJob = true;
                                    }
                                    else
                                    {
                                        canAddThisJob = false;
                                    }
                                }
                                reader.Close();
                                if(!canAddThisJob)
                                    break;
                            }
                            if(canAddThisJob)
                            {
                                jobsReady.Add(job);
                            }
                        }
                    }
                }
                jobs = jobsReady;
                scope.Complete();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                scope.Dispose();
                conn.Close();
            }

            return jobs;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="jobID"></param>
        /// <returns></returns>
        public static Job GetJob(Guid jobID)
        {
            Job job = new Job();
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "GetJob";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter jobIDparam=new SqlParameter("@JobID",jobID.ToString());
                cmd.Parameters.Add(jobIDparam);
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    
                    job.JobID = new Guid(reader["JobID"].ToString());
                    job.RunID = new Guid(reader["RunID"].ToString());
                    job.ComponentID=new Guid(reader["ComponentID"].ToString());
                    job.ComponentTypeName = reader["ComponentTypeName"].ToString();
                    job.Dependency = new List<Guid>();
                    string allDependency = reader["Dependency"].ToString();
                    if (!string.IsNullOrEmpty(allDependency))
                    {
                        string[] depencendies = allDependency.Split(new char[] { ',' });
                        foreach (string dependency in depencendies)
                        {
                            Guid depend = new Guid(dependency);
                            job.Dependency.Add(depend);
                        }
                    }
                    job.Cancelled = bool.Parse(reader["Cancelled"].ToString());
                    job.HasError = bool.Parse(reader["HasError"].ToString());
                    job.IsFirstJob = bool.Parse(reader["IsFirstJob"].ToString());
                    job.IsLastJob = bool.Parse(reader["IsLastJob"].ToString());
                    job.JobInstructionTypeName = reader["JobInstructionTypeName"].ToString();
                    job.JobOutputTypeName = reader["JobOutputTypeName"].ToString();
                    job.JobInstructionID = int.Parse(reader["JobInstructionID"].ToString());
                    job.JobOutputID = int.Parse(reader["JobOutputID"].ToString());
                    job.Priority = int.Parse(reader["Priority"].ToString());
                    job.ReceiveTime = DateTime.Parse(reader["ReceiveTime"].ToString());
                    job.SubmissionTime = DateTime.Parse(reader["SubmissionTime"].ToString());
                    job.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                    job.FinishTime = DateTime.Parse(reader["FinishTime"].ToString());
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            return job;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="runID"></param>
        /// <returns></returns>
        public static List<Job> GetAllJobsForRun(Guid runID)
        {
            List<Job> jobs=new List<Job>();
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "GetAllJobsForRun";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter jobIDparam = new SqlParameter("@RunID", runID.ToString());
                cmd.Parameters.Add(jobIDparam);
                SqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read())
                {
                    Job job = new Job();
                    job.JobID = new Guid(reader["JobID"].ToString());
                    job.RunID = new Guid(reader["RunID"].ToString());
                    job.ComponentID = new Guid(reader["ComponentID"].ToString());
                    job.ComponentTypeName = reader["ComponentTypeName"].ToString();
                    job.Dependency = new List<Guid>();
                    string allDependency = reader["Dependency"].ToString();
                    if (!string.IsNullOrEmpty(allDependency))
                    {
                        string[] depencendies = allDependency.Split(new char[] { ',' });
                        foreach (string dependency in depencendies)
                        {
                            Guid depend = new Guid(dependency);
                            job.Dependency.Add(depend);
                        }
                    }
                    job.Cancelled = bool.Parse(reader["Cancelled"].ToString());
                    job.HasError = bool.Parse(reader["HasError"].ToString());
                    job.IsFirstJob = bool.Parse(reader["IsFirstJob"].ToString());
                    job.IsLastJob = bool.Parse(reader["IsLastJob"].ToString());
                    job.JobInstructionTypeName = reader["JobInstructionTypeName"].ToString();
                    job.JobOutputTypeName = reader["JobOutputTypeName"].ToString();
                    job.JobInstructionID = int.Parse(reader["JobInstructionID"].ToString());
                    job.JobOutputID = int.Parse(reader["JobOutputID"].ToString());
                    job.Priority = int.Parse(reader["Priority"].ToString());
                    job.ReceiveTime = DateTime.Parse(reader["ReceiveTime"].ToString());
                    job.SubmissionTime = DateTime.Parse(reader["SubmissionTime"].ToString());
                    job.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                    job.FinishTime = DateTime.Parse(reader["FinishTime"].ToString());

                    jobs.Add(job);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            return jobs;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="runID"></param>
        /// <param name="componentID"></param>
        /// <returns></returns>
        public static Job GetJobByRunAndComponent(Guid runID, Guid componentID)
        {
            Job job = new Job();
            string connStr = ConfigurationManager.ConnectionStrings["Workflow"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "GetJobByRunAndComponent";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter runIDParam = new SqlParameter("@RunID", runID.ToString());
                SqlParameter componentIDparam=new SqlParameter("@ComponentID",componentID.ToString());
                cmd.Parameters.AddRange(new SqlParameter[]{runIDParam,componentIDparam});
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {

                    job.JobID = new Guid(reader["JobID"].ToString());
                    job.RunID = new Guid(reader["RunID"].ToString());
                    job.ComponentID = new Guid(reader["ComponentID"].ToString());
                    job.ComponentTypeName = reader["ComponentTypeName"].ToString();
                    job.Dependency = new List<Guid>();
                    string allDependency = reader["Dependency"].ToString();
                    if (!string.IsNullOrEmpty(allDependency))
                    {
                        string[] depencendies = allDependency.Split(new char[] { ',' });
                        foreach (string dependency in depencendies)
                        {
                            Guid depend = new Guid(dependency);
                            job.Dependency.Add(depend);
                        }
                    }
                    job.Cancelled = bool.Parse(reader["Cancelled"].ToString());
                    job.HasError = bool.Parse(reader["HasError"].ToString());
                    job.IsFirstJob = bool.Parse(reader["IsFirstJob"].ToString());
                    job.IsLastJob = bool.Parse(reader["IsLastJob"].ToString());
                    job.JobInstructionTypeName = reader["JobInstructionTypeName"].ToString();
                    job.JobOutputTypeName = reader["JobOutputTypeName"].ToString();
                    job.JobInstructionID = int.Parse(reader["JobInstructionID"].ToString());
                    job.JobOutputID = int.Parse(reader["JobOutputID"].ToString());
                    job.Priority = int.Parse(reader["Priority"].ToString());
                    job.ReceiveTime = DateTime.Parse(reader["ReceiveTime"].ToString());
                    job.SubmissionTime = DateTime.Parse(reader["SubmissionTime"].ToString());
                    job.StartTime = DateTime.Parse(reader["StartTime"].ToString());
                    job.FinishTime = DateTime.Parse(reader["FinishTime"].ToString());
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                ExceptionHandler.HandlesException(ex);
            }
            finally
            {
                conn.Close();
            }

            return job;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="job"></param>
        /// <param name="allJobsForRun"></param>
        /// <param name="affectedJobs"></param>
        public static void GetAffectedJobs(Job job, List<Job> allJobsForRun, ref List<Job> affectedJobs)
        {
            foreach (Job downstreamJob in allJobsForRun)
            {
                if (downstreamJob.Dependency.Contains(job.ComponentID) && (!affectedJobs.Contains(downstreamJob)))
                {
                    affectedJobs.Add(downstreamJob);
                    JobDALC.GetAffectedJobs(downstreamJob, allJobsForRun, ref affectedJobs);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="jobID"></param>
        /// <returns></returns>
        public static List<Job> DisableJob(Guid jobID)
        {
            Run run = RunDALC.GetRunByJob(jobID);
            List<Job> allJobsForRun = JobDALC.GetAllJobsForRun(run.RunID);
            List<Job> jobsToDisable = new List<Job>();
            Job currentJob = JobDALC.GetJob(jobID);
            jobsToDisable.Add(currentJob);

            JobDALC.GetAffectedJobs(currentJob, allJobsForRun, ref jobsToDisable);
            foreach (Job jobToDisable in jobsToDisable)
            {
                jobToDisable.ReceiveTime = TimeUtil.DbMaxTime;
                JobDALC.SaveJob(jobToDisable);
            }
            return jobsToDisable;
        }
    }
}
